﻿/*****************************************************************************/
/* Stored Procedure :: LogException()                                        */
/*****************************************************************************/
/* Copyright (C) 2003, Perot Systems Corporation. All right reserved.        */
/* Developer: Dmitry Frenkel (dima.frenkel@ps.net)                           */
/* Started:  07/07/2003                                                      */
/*****************************************************************************/
CREATE PROCEDURE [GrantManagement].[GrantsDetailFor]( @VolunteerID BIGINT )
AS  
BEGIN

DECLARE @keys TABLE (GrantID BIGINT, ApplicantID BIGINT, CaseID BIGINT, ServiceMemberID BIGINT )

Insert Into @keys ( GrantID, ApplicantID, CaseID, ServiceMemberID )
	Select  Grants.OID As GrantID,
			Applicants.OID As ApplicantID,
			Cases.OID As CaseID,
			ServiceMen.OID As ServiceMemberID
		From Grants
		Join Applicants On Grants.ApplicantID = Applicants.OID  			
		Join Cases On Grants.CaseID = Cases.OID
		Join ServiceMen On ServiceMen.OID = Cases.ServiceManID
	Where 
		Cases.CaseVolunteerID = @VolunteerID	

	Select	Grants.OID, 
			Grants.Version As GrantVersion,
			Grants.CaseID,
			Grants.ApplicantID,
			Grants.ValueOfGrant,
			Grants.CheckNumber,
			Grants.NameOnCreditCard,
			Grants.GrantTypeID,
			Grants.StateID,
			Grants.PaymentTypeID,
			Grants.DeliveryMethodID,
			Grants.DeliverToPersonID,
			Grants.DeliverToAddressID,
			Grants.Approver1,
			Grants.ApprovalDate1,
			Grants.Approver2,
			Grants.ApprovalDate2,
			Grants.DisbursedBy,
			Grants.DispersalDate,
			Grants.VerifiedBy,
			Grants.VerificationDate
	From Grants 
		Join @Keys K On Grants.OID = K.GrantID
	
		
	Select	Applicants.OID,
			Applicants.Version as ApplicantVersion,
			Applicants.KinshipID,
			Applicants.AddressID,		   
			People.OID,
			People.Version,
			People.FirstName,
			People.LastName,
			People.MiddleInitial,
			People.DOB,
			People.Sex,
			People.Phone1,
			People.Phone2,
			People.eMailAddress
	From Applicants
        Join People On Applicants.OID = People.OID
	    Join @Keys K On K.ApplicantID = Applicants.OID
		
	Select Cases.OID,
		   Cases.Version As CaseVersion,
		   Cases.ServiceManID,
		   Cases.InjuryDate,
		   Cases.CaseVolunteerID,
		   Cases.CaseDirectorID
	From Cases 
		Join @Keys K On K.CaseID = Cases.OID
		
Select	ServiceMen.OID,
		ServiceMen.Version As ServiceManVersion,	
		People.OID,
		People.Version,
		People.FirstName,
		People.LastName,
		People.MiddleInitial,
		People.DOB,
		People.Sex,
		People.Phone1,
		People.Phone2,
		People.eMailAddress
	From  ServiceMen 
		Join People On ServiceMen.OID = People.OID
	    Join @Keys K On K.ServiceMemberID = ServiceMen.OID

END


